package dao;

import model.Employer;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmployerDao {
    Connection connection;

    {
        try {
            connection = JDBCUtils.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<Employer> findRetire () throws SQLException {
        String sql = "select * from retire ";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet set = statement.executeQuery(sql);
        List<Employer> list = new ArrayList<>();
        while(set.next())
        {
            Employer employer = new Employer();
            employer.setId(set.getInt(1));
            employer.setName(set.getString(2));
            employer.setAge(set.getString(3));
            employer.setSex(set.getString(4));
            employer.setDept(set.getString(5));
            employer.setMarriage(set.getString(6));
            employer.setDegree(set.getString(7));
            employer.setTitle(set.getString(8));
            list.add(employer);
        }
        return list;
    }

    public List<Employer> findAll () throws SQLException {
        String sql = "select * from employer";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet set = statement.executeQuery(sql);
        List<Employer> list = new ArrayList<>();
        while(set.next())
        {
            Employer employer = new Employer();
            employer.setId(set.getInt(1));
            employer.setName(set.getString(2));
            employer.setAge(set.getString(3));
            employer.setSex(set.getString(4));
            employer.setDept(set.getString(5));
            employer.setMarriage(set.getString(6));
            employer.setDegree(set.getString(7));
            employer.setTitle(set.getString(8));
            list.add(employer);
        }
        return list;
    }

    public boolean addEmployer(Employer employer) throws SQLException {
        if (Integer.parseInt(employer.getAge()) < 18 || Integer.parseInt(employer.getAge()) >60) {
            return false;
        }
        String sql = "insert into employer (name , age , sex , dept , marriage , degree , job_title) values (? , ? , ? , ? , ? , ? , ? )";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1 , employer.getName());
        statement.setInt(2 , Integer.parseInt(employer.getAge()));
        statement.setString(3 , employer.getSex());
        statement.setString(4 , employer.getDept());
        statement.setString(5 , employer.getMarriage());
        statement.setString(6 , employer.getDegree());
        statement.setString(7 , employer.getTitle());
        int row = statement.executeUpdate();
        if (row <= 0) {
            return false;
        }
        return true;

    }

    public boolean updateEmployer(Employer employer) throws SQLException {
        if (Integer.parseInt(employer.getAge()) < 18 || Integer.parseInt(employer.getAge()) >60) {
            return false;
        }
        String sql = "update employer set name = ? , age = ? , sex = ? , dept = ? , marriage = ? , degree = ? , job_title = ? where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1 , employer.getName());
        statement.setInt(2 , Integer.parseInt(employer.getAge()));
        statement.setString(3 , employer.getSex());
        statement.setString(4 , employer.getDept());
        statement.setString(5 , employer.getMarriage());
        statement.setString(6 ,employer.getDegree());
        statement.setString(7 ,employer.getTitle());
        statement.setInt(8, employer.getId());
        int row = statement.executeUpdate();
        if (row <= 0) {
            return false;
        }
        return true;
    }

    public boolean deleteByid(int id) throws SQLException {
        String sql = "delete from employer where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1 , id);
        int row = statement.executeUpdate();
        if (row <= 0) {
            return false;
        }
        return true;

    }

    public Employer findById(int id) throws SQLException {
        String sql = " select * from employer where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt( 1,id);
        ResultSet set = statement.executeQuery();
        List<Employer> list = new ArrayList<>();
        while(set.next())
        {
            Employer employer = new Employer();
            employer.setId(set.getInt(1));
            employer.setName(set.getString(2));
            employer.setAge(set.getString(3));
            employer.setSex(set.getString(4));
            employer.setDept(set.getString(5));
            employer.setMarriage(set.getString(6));
            employer.setDegree(set.getString(7));
            employer.setTitle(set.getString(8));
            list.add(employer);
        }
        return list.get(0);
    }



    public EmployerDao()  {
    }


    public List<Employer> findByDegree (String degree) throws SQLException {
        String sql = "select * from employer where degree = ? ";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1 , degree);
        ResultSet set = statement.executeQuery();
        List<Employer> list = new ArrayList<>();
        while(set.next())
        {
            Employer employer = new Employer();
            employer.setId(set.getInt(1));
            employer.setName(set.getString(2));
            employer.setAge(set.getString(3));
            employer.setSex(set.getString(4));
            employer.setDept(set.getString(5));
            employer.setMarriage(set.getString(6));
            employer.setDegree(set.getString(7));
            employer.setTitle(set.getString(8));
            list.add(employer);
        }
        return list;
    }


    public List<Employer> findByMarriage (String degree) throws SQLException {
        String sql = "select * from employer where marriage = ? ";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1 , degree);
        ResultSet set = statement.executeQuery();
        List<Employer> list = new ArrayList<>();
        while(set.next())
        {
            Employer employer = new Employer();
            employer.setId(set.getInt(1));
            employer.setName(set.getString(2));
            employer.setAge(set.getString(3));
            employer.setSex(set.getString(4));
            employer.setDept(set.getString(5));
            employer.setMarriage(set.getString(6));
            employer.setDegree(set.getString(7));
            employer.setTitle(set.getString(8));
            list.add(employer);
        }
        return list;
    }


    public List<Employer> findByDept (String degree) throws SQLException {
        String sql = "select * from employer where dept = ? ";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1 , degree);
        ResultSet set = statement.executeQuery();
        List<Employer> list = new ArrayList<>();
        while(set.next())
        {
            Employer employer = new Employer();
            employer.setId(set.getInt(1));
            employer.setName(set.getString(2));
            employer.setAge(set.getString(3));
            employer.setSex(set.getString(4));
            employer.setDept(set.getString(5));
            employer.setMarriage(set.getString(6));
            employer.setDegree(set.getString(7));
            employer.setTitle(set.getString(8));
            list.add(employer);
        }
        return list;
    }

}
